# -*- coding:utf8 -*- #
# -----------------------------------------------------------------------------------
# ProjectName:  smart-water-iot-test
# FileName:     mySqlPool.py
# Author  :
# Datetime:     2023/3/22 10:01
# Description：
# -----------------------------------------------------------------------------------
# coding=utf-8
"""
使用DBUtils数据库连接池中的连接，操作数据库
OperationalError: (2006, ‘MySQL server has gone away’)
"""
import sshtunnel
import pymysql
from dbutils.pooled_db import PooledDB
from sshtunnel import SSHTunnelForwarder
from common.util.configUtil.configUtil import ConfigUtil
from common.util.jsonUtl.jsonUtil import JsonUtil
from common.util.logUtil.logUtil import LoggerUtil


class MysqlPoolUtil(object):

    def __init__(self, logType: int = 1):
        self._conn = None
        self.cursor = None
        self.logger = LoggerUtil(logType)

    def connect(self):
        """
                    mincached:连接池中空闲连接的初始数量
                    maxcached:连接池中空闲连接的最大数量
                    maxshared:共享连接的最大数量
                    maxconnections:创建连接池的最大数量
                    blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
                    maxusage:单个连接的最大重复使用次数
                    setsession:optional list of SQL commands that may serve to prepare
                        the session, e.g. ["set datestyle to ...", "set time zone ..."]
                    reset:how connections should be reset when returned to the pool
                        (False or None to rollback transcations started with begin(),
                        True to always issue a rollback for safety's sake)
                    host:数据库ip地址
                    port:数据库端口
                    db:库名
                    user:用户名
                    passwd:密码
                    charset:字符编码
                """
        try:
            config = ConfigUtil()
            dataBaseEnvironment = config.get_database_status()

            if dataBaseEnvironment is True:
                sshProxyHost = config.get_database_info("SSH_Proxy_host")
                sshProxyPort = int(config.get_database_info("SSH_Proxy_port"))  # 端口号需要是整型
                sshProxyUser = config.get_database_info("SSH_Proxy_user")
                sshProxyPassword = config.get_database_info("SSH_Proxy_password")
                proHost = config.get_database_info("Pro_host")
                proPort = int(config.get_database_info("Pro_port"))  # 端口号需要是整型
                proUser = config.get_database_info("Pro_user")
                proPassword = config.get_database_info("Pro_password")
                proDatabase = config.get_database_info("Pro_database")
                server = SSHTunnelForwarder(
                    (sshProxyHost, sshProxyPort),  # 这里写入B 跳板机IP、端口
                    ssh_username=sshProxyUser,  # 跳板机 用户名
                    ssh_password=sshProxyPassword,  # 跳板机 密码
                    remote_bind_address=(proHost, proPort),
                    # 这里写入 C数据库的 IP、端口号
                )
                server.start()
                __pool = PooledDB(pymysql, mincached=10, maxcached=20, maxshared=10,
                                  maxconnections=200,
                                  blocking=True,
                                  maxusage=100, setsession=None, reset=True,
                                  host="127.0.0.1", port=server.local_bind_port, db=proDatabase,
                                  user=proUser, passwd=proPassword,
                                  charset='utf8mb4'
                                  )
                self._conn = __pool.connection()
                return self._conn
            else:
                testUser = config.get_database_info("Test_user")
                testPassword = config.get_database_info("Test_password")
                testHost = config.get_database_info("Test_host")
                testPort = int(config.get_database_info("Test_port"))  # 端口号需要是整型
                testDatabase = config.get_database_info("Test_database")
                __pool = PooledDB(pymysql, mincached=10, maxcached=20, maxshared=10,
                                  maxconnections=500,
                                  blocking=True,
                                  maxusage=100, setsession=None, reset=True,
                                  host=testHost, port=testPort, db=testDatabase,
                                  user=testUser, passwd=testPassword,
                                  charset='utf8mb4'
                                  )
                self._conn = __pool.connection()
                return self._conn
        except sshtunnel.HandlerSSHTunnelForwarderError as handlerError:
            self.logger.error(f"sshtunnel.HandlerSSHTunnelForwarderError:  {handlerError}")
            self.connect()
        except sshtunnel.BaseSSHTunnelForwarderError as baseError:
            self.logger.error(f"sshtunnel.BaseSSHTunnelForwarderError:  {baseError}")
            self.connect()
        except Exception as error:
            self.logger.error(f"Exception:  {error}")
            self.connect()

    def close_connect(self, connect, cursor):
        try:
            connect.close()
            cursor.close()
        except Exception as e:
            print(e)
            self.logger.error(f"close:  {e}")


def select_sql(sql, logType: int = 1) -> list:
    """查结果"""
    try:
        mysql = MysqlPoolUtil(logType)
        connect = mysql.connect()
        cursor = connect.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        allResult = []
        for i in result:
            row_list = []
            for k in i:
                if k == "" or k is None:
                    k = 0
                row_list.append(k)
            allResult.append(row_list)
        mysql.close_connect(connect, cursor)
        return allResult
    except RecursionError as e:
        print("maximum recursion depth exceeded while calling a Python object！(数据库查询超出最大递归深度)")
        mysql.logger.error("maximum recursion depth exceeded while calling a Python object！(数据库查询超出最大递归深度)")


if __name__ == "__main__":
    read_json = JsonUtil()
    sql = read_json.read_sql("info")
    print(sql)
    result = select_sql(sql=sql, logType=1)
    print(result)
